﻿using SchoolCommunicationSystem.Models;
using SCS.Business;
using SCS.Common.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using OfficeOpenXml.Drawing.Chart;
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Runtime.InteropServices;
using System.Drawing;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using System.Drawing.Imaging;
using OfficeOpenXml.Style;
using OfficeOpenXml.Style.XmlAccess;
using OfficeOpenXml.Table;
using System.Web;
using LinqToExcel;
using System.Data.OleDb;
using System.Data;

namespace SchoolCommunicationSystem.Controllers
{
    public class TeacherViewController : Controller
    {
        private TeacherServices _teacherServices = new TeacherServices();
        private MembershipService _memberServices = new MembershipService();

        /// <summary>
        /// Pre ViewGradesByDate
        /// </summary>
        /// <returns>View</returns>
        public ActionResult ViewGradesByDate()
        {
            TeacherViewModels TeacherViewModel = new TeacherViewModels();

            string userName = User.Identity.Name;
            var teacherUser = _memberServices.GetUser(userName);
            int classOfHomeroomTeacherID = teacherUser.Teachers.FirstOrDefault().Classes.FirstOrDefault().classID;

            TeacherViewModel.grade = _teacherServices.GradeByTeacherID(classOfHomeroomTeacherID);
            TeacherViewModel.createDate = null;
            TeacherViewModel.firstDate = null;
            TeacherViewModel.lastDate = null;

            return View(TeacherViewModel);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="firstDate">firstDate</param>
        /// <param name="lastDate">lastDate</param>
        /// <param name="page">1</param>
        /// <param name="sortDir">ASC</param>
        /// <returns>view</returns>
        [HttpPost]
        public ActionResult ViewGradesByDate(DateTime firstDate, DateTime lastDate, int page = 1, string sortDir = "ASC")
        {
            string userName = User.Identity.Name;
            var teacherUser = _memberServices.GetUser(userName);
            int classOfHomeroomTeacherID = teacherUser.Teachers.FirstOrDefault().Classes.FirstOrDefault().classID;

            const int pageSize = 10;
            var totalRows = _teacherServices.CountTeacherViewGrade(firstDate, lastDate, classOfHomeroomTeacherID);
            if (totalRows <= pageSize)
            {
                page = 1;
            }
            sortDir = sortDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? sortDir : "asc";
            IList<Grade> grades = null;
            if (firstDate == null || lastDate == null)
            {
                grades = _teacherServices.GradeByTeacherID(classOfHomeroomTeacherID);
            }
            else
            {
                grades = _teacherServices.GradeByTeacherID(firstDate, lastDate, classOfHomeroomTeacherID);
            }
            var data = new TeacherViewModels()
            {
                TotalRows = totalRows,
                PageSize = pageSize,
                grade = grades
            };
            return View(data);
        }

        /// <summary>
        /// Pre ViewCommentByDate
        /// </summary>
        /// <returns>View</returns>
        public ActionResult ViewCommentByDate()
        {
            TeacherViewModels TeacherViewModel = new TeacherViewModels();
            TeacherViewModel.commentInClass = _teacherServices.CommentInClass();
            TeacherViewModel.dateOfComment = null;
            TeacherViewModel.firstDate = null;
            TeacherViewModel.lastDate = null;

            return View(TeacherViewModel);
        }

        /// <summary>
        /// ViewCommentByDate
        /// </summary>
        /// <param name="firstDate">firstDate</param>
        /// <param name="lastDate">lastDate</param>
        /// <param name="page">1</param>
        /// <param name="sortDir">ASC</param>
        /// <returns>view</returns>
        [HttpPost]
        public ActionResult ViewCommentByDate(string firstDate, string lastDate, int page = 1, string sortDir = "ASC")
        {
            DateTime firstDates = DateTime.Now;
            DateTime lastDates = DateTime.Now;
            string[] formats = { "dd/MM/yyyy", "dd/M/yyyy", "d/M/yyyy", "d/MM/yyyy",
                    "dd/MM/yy", "dd/M/yy", "d/M/yy", "d/MM/yy"};
            DateTime.TryParseExact(firstDate, formats, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out firstDates);
            DateTime.TryParseExact(lastDate, formats, System.Globalization.CultureInfo.InvariantCulture, System.Globalization.DateTimeStyles.None, out lastDates);
            const int pageSize = 10;

            var totalRows = _teacherServices.CountTeacherViewComment(firstDates, lastDates);
            if (totalRows <= pageSize)
            {
                page = 1;
            }
            sortDir = sortDir.Equals("desc", StringComparison.CurrentCultureIgnoreCase) ? sortDir : "asc";
            IList<CommentInClass> commentInClass = null;
            if (firstDate == null || lastDate == null)
            {
                commentInClass = _teacherServices.CommentInClass();
            }
            else
            {
                commentInClass = _teacherServices.CommentInClass(page, pageSize, firstDates, lastDates);
            }
            var data = new TeacherViewModels()
            {
                TotalRows = totalRows,
                PageSize = pageSize,
                commentInClass = commentInClass
            };
            return View(data);
        }

        /// <summary>
        /// Pre import a list of grade
        /// </summary>
        /// <returns>view</returns>
        public ActionResult ImportGrades()
        {
            return View();
        }

        /// <summary>
        /// import a list of grade
        /// </summary>
        /// <param name="fc">FormCollection</param>
        /// <param name="uploadFile">HttpPostedFileBase</param>
        /// <returns>View</returns>
        [HttpPost]
        public ActionResult ImportGrades(FormCollection fc, HttpPostedFileBase uploadFile)
        {
            try
            {
                if (uploadFile.ContentLength > 0)
                {
                    string filePath = Path.Combine(HttpContext.Server.MapPath("~/Uploads"),
                    Path.GetFileName(uploadFile.FileName));
                    uploadFile.SaveAs(filePath);

                    var excel = new ExcelQueryFactory();
                    excel.FileName = filePath;

                    string conn;

                    conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
                    ("Data Source=" + filePath + ";" +
                    "Extended Properties=\"Excel 12.0;\""));
                    OleDbConnection oleDBCon = new OleDbConnection(conn);
                    oleDBCon.Open();
                    DataTable dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string excelsheetname = dt.Rows[0].ItemArray[2].ToString();
                    string SSQL = "SELECT * from [Sheet1$]";

                    OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
                    DataSet ds = new DataSet();
                    oleDA.Fill(ds);
                    DataTable _DtTable = ds.Tables[0]; // or [ ds ]
                    oleDBCon.Close();
                    string subjectName = _DtTable.Columns[3].ColumnName;
                    byte subjectID = _teacherServices.GetSubjectIDBySubjectName(subjectName).subjectID;
                    for (int i = 1; i < _DtTable.Rows.Count; i++)
                    {
                        if (!string.IsNullOrEmpty(_DtTable.Rows[i][2].ToString()))
                        {
                            Grade grade = new Grade();
                            grade.learnerID = Int32.Parse(_DtTable.Rows[i][0].ToString());
                            grade.subjectID = subjectID;
                            grade.gradeTypeID = _teacherServices.GetGradeTypeIDByGradeTypeName(_DtTable.Rows[i][3].ToString()).gradetypeID;
                            grade.termID = _teacherServices.GetAllTerm().Select(m => m.termID).Max();
                            grade.createDate = DateTime.Now;
                            grade.grade1 = double.Parse(_DtTable.Rows[i][2].ToString());
                            grade.isSent = false;

                            _teacherServices.AddGrade(grade);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string a = ex.Message;
            }
            return RedirectToAction("ViewGradesByDate");
        }

        /// <summary>
        /// Statistic
        /// </summary>
        /// <returns>View</returns>
        public ActionResult Statistic()
        {
            StatisticModels statisticModel = new StatisticModels();
            //get teacher account
            string userName = User.Identity.Name;
            var teacherUser = _memberServices.GetUser(userName);
            //int teacherID = teacherUser.Teachers.FirstOrDefault().teacherID;
            int classID = teacherUser.Teachers.FirstOrDefault().Classes.FirstOrDefault().classID;

            //get data of teacher's class
            IEnumerable<Learner> learnersData = _teacherServices.GetAllLearnerDataToStatistic(classID);
            statisticModel.classID = classID;
            statisticModel.Learners = learnersData;
            statisticModel.className = teacherUser.Teachers.FirstOrDefault().Classes.FirstOrDefault().ClassInSchoolYears.FirstOrDefault().className;
            statisticModel.teacherName = teacherUser.Teachers.FirstOrDefault().teacherName;
            statisticModel.schoolYearName = learnersData.FirstOrDefault().Class.ClassInSchoolYears.FirstOrDefault().SchoolYear.schoolYearName;

            return View(statisticModel);
        }

        /// <summary>
        /// Statistic
        /// </summary>
        /// <param name="statisticModel">StatisticModels</param>
        /// <returns>View</returns>
        [HttpPost]
        public ActionResult Statistic(StatisticModels statisticModel)
        {
            //get learner data
            IEnumerable<Learner> learnersData = _teacherServices.GetAllLearnerDataToStatistic(statisticModel.classID);
            statisticModel.Learners = learnersData;

            DirectoryInfo outputDir = new DirectoryInfo(@"D:");

            //////////////////////////
            FileInfo newFile = new FileInfo(outputDir.FullName + @"\ThongKeLop" + statisticModel.className + ".xlsx");
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                newFile = new FileInfo(outputDir.FullName + @"\ThongKeLop" + statisticModel.className + ".xlsx");
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("ThongKe" + statisticModel.className);
                //Add the headers
                worksheet.Cells[1, 1].Value = "Mã HS";
                worksheet.Cells[1, 2].Value = "Tên HS";
                worksheet.Cells[1, 3].Value = "Giới tính";
                worksheet.Cells[1, 4].Value = "Lớp";
                worksheet.Cells[1, 5].Value = "Nói chuyện";
                worksheet.Cells[1, 6].Value = "Không làm bài";
                worksheet.Cells[1, 7].Value = "Nghỉ học";

                //Add some items...
                int i = 2;
                foreach (var m in statisticModel.Learners)
                {
                    if (m.learnerID != 0)
                    {
                        worksheet.Cells["A" + i.ToString()].Value = m.learnerID;
                        worksheet.Cells["B" + i.ToString()].Value = m.firstName + " " + m.lastName;
                        worksheet.Cells["C" + i.ToString()].Value = m.gender;
                        worksheet.Cells["D" + i.ToString()].Value = statisticModel.className;
                        worksheet.Cells["E" + i.ToString()].Value = _teacherServices.CountLearnerConsciousness(1, m.learnerID);
                        worksheet.Cells["F" + i.ToString()].Value = _teacherServices.CountLearnerConsciousness(2, m.learnerID);
                        worksheet.Cells["G" + i.ToString()].Value = _teacherServices.CountLearnerConsciousness(3, m.learnerID);
                        i++;
                    }
                }

                //Add a formula for the value-column
               // worksheet.Cells["E2:E4"].Formula = "C2*D2";

                //Ok now format the values;
                using (var range = worksheet.Cells[1, 1, 1, 7])
                {
                    range.Style.Font.Bold = true;
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
                    range.Style.Font.Color.SetColor(Color.White);
                }

                //worksheet.Cells["A7:E7"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                //worksheet.Cells["A7:E7"].Style.Font.Bold = true;


                //Create an autofilter for the range
                worksheet.Cells["A1:G1"].AutoFilter = true;

                //worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@";   //Format as text
                worksheet.Cells.AutoFitColumns(0);  //Autofit columns for all cells

                // lets set the header text 
                worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\"ThongKe";
                // add the page number to the footer plus the total number of pages
                worksheet.HeaderFooter.OddFooter.RightAlignedText =
                    string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                // add the sheet name to the footer
                worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
                // add the file path to the footer
                worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;

                worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
                worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];

                // Change the sheet view to show it in page layout mode
                worksheet.View.PageLayoutView = true;

                // set some document properties
                package.Workbook.Properties.Title = "Statistic";
                package.Workbook.Properties.Author = "SCSTeam";
                package.Workbook.Properties.Comments = "Statistic for Class";

                // set some extended property values
                package.Workbook.Properties.Company = "SCSTeam";

                // set some custom property values
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "SCSTeam");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

                worksheet.View.PageLayoutView = false;
                // save our new workbook and we are done!
                package.Save();

            }

            /////////////



            /*FileInfo templateFile = new FileInfo(outputDir.FullName + @"\ThongKe.xlsx");
            FileInfo newFile = new FileInfo(outputDir.FullName + @"\ThongKeLop" + statisticModel.className + ".xlsx");
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                newFile = new FileInfo(outputDir.FullName + @"\ThongKeLop" + statisticModel.className + ".xlsx");
            }
            using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
            {
                //Open worksheet 1
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                /*worksheet.InsertRow(5, 2);

                worksheet.Cells["A5"].Value = "12010";
                worksheet.Cells["B5"].Value = "Drill";
                worksheet.Cells["C5"].Value = 20;
                worksheet.Cells["D5"].Value = 8;
                worksheet.Cells["E5"].Value = 8;

                worksheet.Cells["A6"].Value = "12011";
                worksheet.Cells["B6"].Value = "Crowbar";
                worksheet.Cells["C6"].Value = 7;
                worksheet.Cells["D6"].Value = 23.48;

                worksheet.Cells["E2:E6"].FormulaR1C1 = "RC[-2]*RC[-1]";

                var name = worksheet.Names.Add("SubTotalName", worksheet.Cells["C7:E7"]);
                name.Style.Font.Italic = true;
                name.Formula = "SUBTOTAL(9,C2:C6)";

                //Format the new rows
                worksheet.Cells["C5:C6"].Style.Numberformat.Format = "#,##0";
                worksheet.Cells["D5:E6"].Style.Numberformat.Format = "#,##0.00";

                var chart = (worksheet.Drawings.AddChart("PieChart", eChartType.Pie3D) as ExcelPieChart);

                chart.Title.Text = "Giới tính";
                //From row 1 colum 5 with five pixels offset
                chart.SetPosition(0, 0, 10, 5);
                chart.SetSize(600, 300);

                ExcelAddress valueAddress = new ExcelAddress(2, 5, 6, 5);
                var ser = (chart.Series.Add(valueAddress.Address, "B2:B6") as ExcelPieChartSerie);
                chart.DataLabel.ShowCategory = true;
                chart.DataLabel.ShowPercent = true;

                chart.Legend.Border.LineStyle = eLineStyle.Solid;
                chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
                chart.Legend.Border.Fill.Color = Color.DarkBlue;

                //Switch the PageLayoutView back to normal
                
                // save our new workbook and we are done!
                package.Save();
            }*/
            TempData["Msg"] = "Đã xuất dữ liệu thống kê tại địa chỉ: " + newFile.FullName;
            return View(statisticModel);
        }
    }
}

